from flask import request, Blueprint
from common.log import log_handler
from common.database import OLTP
from common.utils.format_util import err_formatter
import json
import pandas as pd
from common.database import OLAP
import numpy as np
from osgeo import osr

log = log_handler.LogHandler().get_log()

gis = Blueprint('gis', __name__)


@gis.route('/kgis_transform', methods=['POST'])
def kgis_trans():
    params = request.form.to_dict()
    kg_detail = params["kgDetail"]

    # file = open('api/results.json', 'w')
    # file.write(kg_detail)
    # file.close()

    tb = params["tbName"]
    # key = params["key"]
    key = "位置"

    res_dict = {}
    layer_list = []
    # todo json to df
    # df = pd.DataFrame()
    kg_detail_json = json.loads(kg_detail)
    # with open('results.json', 'w') as result_file:
    #     json.dump(kg_detail, result_file)
    try:
        df = get_vertices_dot_layer_df(kg_detail_json, 4326)
        if df.shape[0] > 0:
            table_v = tb + "_v"
            OLAP.save_dataframe(table_v, df)
            table_info_v = {"name": "entity_layer_point", "type": "circle", "tbname": table_v}
            layer_list.append(table_info_v)

        df_e, df_p = get_edges_line_layer_df(kg_detail_json, 4326, key)
        if df_e.shape[0] > 0:
            table_e = tb + "_r"
            OLAP.save_dataframe(table_e, df_e)
            table_info_e = {"name": "relation_layer", "type": "line", "tbname": table_e}
            layer_list.append(table_info_e)
        if df_p.shape[0] > 0:
            df_p = df_p.drop(['id'], axis=1)
            df_p = df_p.rename(columns={'vid': 'id'})
            table_p = tb + "_v_p"
            df_p = add_record_id_(df_p)
            OLAP.save_dataframe(table_p, df_p)
            table_info_p = {"name": "entity_layer_polygon", "type": "fill", "tbname": table_p}
            layer_list.append(table_info_p)

    except Exception as e:
        log.error(e)
        return err_formatter(e)
    res_dict["layers"] = layer_list
    return res_dict


def get_edges_line_layer_df(kg_detail, srid, key):
    vertices_df = pd.DataFrame(kg_detail["vertices"])[["id", "label", "class", "attrs"]]
    ids = []
    geoms = []
    polygons = []
    for i in range(len(vertices_df)):
        attrs = vertices_df.loc[i, "attrs"]
        if key in attrs.keys():
            layer_id, geom_id = attrs[key].split("_")
            source_table, type = OLTP.execute_query(f"select source_table, type from gis_layers where id = {layer_id}").values[
                0]
            data = OLAP.execute_query(
                f"select \"Id\", name, geom, ST_Centroid(geom) as geom_point from {source_table} where \"Id\"={geom_id}").values[
                0]
            if "简称" not in attrs:
                attrs["简称"] = vertices_df.loc[i, "label"]
            polygons.append(data[:-1].tolist() + vertices_df.loc[i].values.tolist()[:-1] + [attrs["简称"]])
            geoms.append(data[-1])
            ids.append(i)
        elif '经度' in attrs and '纬度' in attrs:
            lon = attrs['经度']
            lat = attrs['纬度']
            geom = OLAP.execute_query(f"select ST_SetSRID(st_point({lon},{lat}),{srid})").values[0][0]
            geoms.append(geom)
            ids.append(i)
    polygon_df = pd.DataFrame(polygons, columns=["id", "name", "geom", "vid", "label", "class", "简称"])
    filtered_df = vertices_df.loc[ids]
    filtered_df["geom"] = geoms
    # log.info(filtered_df)

    edges_df = pd.DataFrame(kg_detail["edges"])[["id", "label", "attrs", "source", "target"]]
    # log.info(edges_df)
    source_df = pd.merge(filtered_df, edges_df, left_on="id", right_on="source")[["id_y", "geom"]]
    target_df = pd.merge(filtered_df, edges_df, left_on="id", right_on="target")[
        ["id_y", "label_y", "attrs_y", "geom"]]
    # log.info(source_df)
    # log.info(target_df)
    merge_df = pd.merge(source_df, target_df, on="id_y")
    row_num = len(merge_df)
    if row_num == 0:
        return merge_df, polygon_df
    merge_df.columns = ["id", "geom_x", "label", "attrs", "geom_y"]
    output_table = "dataset.test_kg"
    OLAP.save_dataframe(output_table, merge_df)
    ret_df = OLAP.execute_query(f"select *, st_makeline(geom_x::geometry, geom_y::geometry) as geom from {output_table}")

    ret_df = ret_df[["id", "label", "geom"]]
    df_columns = list(ret_df.columns)
    if "_record_id_" not in df_columns:
        df_columns = list(ret_df.columns)
        df_columns.insert(0, "_record_id_")
        ret_df = ret_df.reindex(columns=df_columns)
        ret_df['_record_id_'] = np.arange(1, row_num + 1)
    return ret_df, polygon_df


def get_vertices_dot_layer_df(map_json, srid):
    vertices = map_json["vertices"]
    filter_data = []
    # 筛选出有gis数据的
    for i in range(len(vertices)):
        if '经度' in vertices[i]["attrs"] and '纬度' in vertices[i]["attrs"]:
            filter_data.append(vertices[i])
    # 创建df组装csv
    gis_data = []
    for i in range(len(filter_data)):
        temp = {}
        temp["id"] = filter_data[i]["id"]
        temp["label"] = filter_data[i]["label"]
        temp["class"] = filter_data[i]["class"]
        temp["经度"] = filter_data[i]["attrs"]["经度"]
        temp["纬度"] = filter_data[i]["attrs"]["纬度"]
        if "简称" in filter_data[i]["attrs"]:
            temp["简称"] = filter_data[i]["attrs"]["简称"]
        else:
            temp["简称"] = filter_data[i]["label"]
        get_geom_sql = "select st_geomfromtext('POINT(" + str(temp["经度"]) + " " + str(
            temp["纬度"]) + ")', " + str(srid) + ");"
        geom_result = OLAP.execute_query(get_geom_sql).values[0][0]
        temp["geom"] = str(geom_result).replace("(", "").replace(")", "").replace(",", "").replace("'", "")
        gis_data.append(temp)
        df = pd.json_normalize(gis_data)

    import numpy as np
    row_num = len(filter_data)
    df_columns = list(df.columns)
    if "_record_id_" not in df_columns:
        df_columns = list(df.columns)
        df_columns.insert(0, "_record_id_")
        df = df.reindex(columns=df_columns)
        df['_record_id_'] = np.arange(1, row_num + 1)
    return df


def add_record_id_(df):
    import numpy as np
    row_num = len(df)
    df_columns = list(df.columns)
    if "_record_id_" not in df_columns:
        df_columns = list(df.columns)
        df_columns.insert(0, "_record_id_")
        df = df.reindex(columns=df_columns)
        df['_record_id_'] = np.arange(1, row_num + 1)
    return df


@gis.route('/prj_file_to_proj4', methods=['POST'])
def prj_file_to_proj4():
    params = request.form.to_dict()
    prj_file_text = params["prj_file_text"]
    print('prj_file_text: %s' % (prj_file_text))
    #Albers坐标系可能无法识别，需转换为Albers_Conic_Equal_Area
    prj_file_text = prj_file_text.replace('PROJECTION["Albers"]', 'PROJECTION["Albers_Conic_Equal_Area"]')
    srs = osr.SpatialReference()
    srs.ImportFromWkt(prj_file_text)
    result = srs.ExportToProj4()
    print('convert_result: %s' % (result))
    return result

